package com.yami.shop.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONValidator;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.yami.shop.bean.enums.CategoryLevel;
import com.yami.shop.bean.enums.DeliveryTemplateType;
import com.yami.shop.bean.enums.ProdStatusEnums;
import com.yami.shop.bean.enums.ProdType;
import com.yami.shop.bean.event.GetTransportNamesEvent;
import com.yami.shop.bean.model.*;
import com.yami.shop.bean.param.*;
import com.yami.shop.common.bean.LangConfig;
import com.yami.shop.common.bean.LangItemConfig;
import com.yami.shop.common.bean.SysProdConfig;
import com.yami.shop.common.config.Constant;
import com.yami.shop.common.exception.YamiShopBindException;
import com.yami.shop.common.i18n.I18nMessage;
import com.yami.shop.common.i18n.LanguageEnum;
import com.yami.shop.common.util.Json;
import com.yami.shop.common.util.PageParam;
import com.yami.shop.common.util.PoiExcelUtil;
import com.yami.shop.config.ShopConfig;
import com.yami.shop.dao.ProductMapper;
import com.yami.shop.manager.impl.LangManager;
import com.yami.shop.service.*;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.jetbrains.annotations.NotNull;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author yami
 */
@Service
@AllArgsConstructor
@Slf4j
@EnableAspectJAutoProxy(proxyTargetClass = true, exposeProxy = true)
public class ProductExcelServiceImpl implements ProductExcelService {

    private final ProductMapper productMapper;
    private final ProductService productService;
    private final CategoryService categoryService;
    private final CategoryLangService categoryLangService;
    private final ApplicationContext applicationContext;
    private final SkuService skuService;
    private final Snowflake snowflake;
    private final CategoryShopService categoryShopService;
    private final SysConfigService sysConfigService;
    private final LangManager langManager;
    private static final String SEMICOLON = ";";
    private static final String COLON = ":";
    private static final Integer VALID_NUM = 2;

    private final ShopConfig shopConfig;

    private final static Pattern IMG_URL = Pattern
            .compile(".*?(image/gif|image/jpeg|image/png|image/jpg|image/bmp)");

    private final static Pattern IMG_URL_suffix = Pattern
            .compile(".*?(gif|jpeg|png|jpg|bmp)");

    @Override
    public int writerProdToExcel(List<ProductExportParam> products, ExcelWriter writer, Long shopId, Integer row, Map<Long, String> categoryMap, LangConfig langConfig) {
        if (CollUtil.isEmpty(products)) {
            return row;
        }
        Integer lang = I18nMessage.getLang();
        List<Long> prodIds = products.stream().map(ProductExportParam::getProdId).collect(Collectors.toList());
        List<ProductExportParam> productExportParams = productMapper.listProdsByProdParam(prodIds);
        if (CollUtil.isEmpty(productExportParams)) {
            return row;
        }
        // 获取店铺分类数据
        Set<Long> shopCategoryIds = productExportParams.stream().filter(productExportParam -> Objects.nonNull(productExportParam.getShopCategoryId())).map(ProductExportParam::getShopCategoryId).collect(Collectors.toSet());
        List<Category> shopCategories = categoryService.listByCategoryIds(shopCategoryIds);
        Map<Long, String> shopCategoryMap = shopCategories.stream().filter(category -> Objects.nonNull(category.getCategoryId())).collect(Collectors.toMap(Category::getCategoryId, Category::getCategoryName));


        List<Sku> skuData = skuService.listSkuAndLangByProdIds(prodIds);
        Map<Long, List<Sku>> skuMap = skuData.stream().filter(sku -> Objects.nonNull(sku.getSkuId())).collect(Collectors.groupingBy(Sku::getProdId));
        // 商品国际化处理
        Map<Long, ProductExportParam> prodMap = productExportParams.stream().collect(Collectors.toMap(ProductExportParam::getProdId, p -> p));
        for (ProductExportParam esProd : products) {
            ProductExportParam prod = prodMap.get(esProd.getProdId());
            List<Sku> skuList = skuMap.get(esProd.getProdId());
            if (Objects.isNull(prod) || CollUtil.isEmpty(skuList)) {
                continue;
            }
            int firstRow = row + 1;
            int lastRow = row + skuList.size();
            int col = -1;
            col = writerProdData(writer, shopId, prod, esProd, firstRow, lastRow, col, categoryMap, shopCategoryMap,langConfig, lang);
            // 商品参数
            for (Sku sku : skuList) {
                int index = col;
                row++;
                // 商品中文名称
//                writer.writeCellValue(++index, row, sku.getProdName());
//                // 商品英文名称
//                writer.writeCellValue(++index, row, prod.getLang() == 1 ? sku.getProdName() : null);
                Map<Integer, SkuLang> skuLangMap;
                if (CollUtil.isEmpty(sku.getSkuLangList())) {
                    skuLangMap = new HashMap<>();
                    System.out.println();
                } else {
                    skuLangMap = sku.getSkuLangList().stream().filter(skuLang -> Objects.nonNull(skuLang.getLang())).collect(Collectors.toMap(SkuLang::getLang, p -> p));
                }

                if (MapUtil.isNotEmpty(skuLangMap) && !skuLangMap.keySet().contains(langConfig.getLang())) {
                    SkuLang newSkuLang = new SkuLang();
                    newSkuLang.setLang(langConfig.getLang());
                    SkuLang skuLang = skuLangMap.get(langManager.getLang(skuLangMap.keySet(), langConfig, lang));
                    newSkuLang.setProdName(skuLang.getProdName());
                    newSkuLang.setProperties(skuLang.getProperties());
                }
                // 商品名称
                for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
                    SkuLang skuLang = skuLangMap.get(langItemConfig.getLang());
                    if (Objects.nonNull(skuLang)) {
                        writer.writeCellValue(++index, row, skuLang.getProdName());
                    } else {
                        writer.writeCellValue(++index, row, "");
                    }
                }
                // 属性名称
                for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
                    SkuLang skuLang = skuLangMap.get(langItemConfig.getLang());
                    if (Objects.nonNull(skuLang)) {
                        writer.writeCellValue(++index, row, skuLang.getProperties());
                    } else {
                        writer.writeCellValue(++index, row, "");
                    }
                }
//                writer.writeCellValue(++index, row, sku.getProperties());
                // 原价
                writer.writeCellValue(++index, row, sku.getOriPrice());
                // 实际价格
                writer.writeCellValue(++index, row, sku.getPrice());
                // 库存
                writer.writeCellValue(++index, row, sku.getStocks());
                // 商家编码
                writer.writeCellValue(++index, row, sku.getPartyCode());
                // 商品重量
                writer.writeCellValue(++index, row, sku.getWeight());
                // 商品体积
                writer.writeCellValue(++index, row, sku.getVolume());
                //库存预警
                writer.writeCellValue(++index,row,sku.getStockWarning());
            }
        }
        return row;
    }

    @Override
    public ExcelWriter getProdExcelWriter(Long shopId, LangConfig langConfig, boolean template) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 商品导出or模板
        List<String> headerList = getHeaderList(shopId, langConfig, template);
        getSheet(writer, shopId, langConfig.getLangItemList().size());
        writer.merge(headerList.size() - 1, "商品信息整理");
        writer.writeRow(headerList);
        return writer;
    }

    @Override
    public void downloadModel(HttpServletResponse response, Long shopId) {
        LangConfig langConfig = langManager.getLangConfig();
        //通过工具类创建writer
        ExcelWriter writer = getProdExcelWriter(shopId, langConfig, true);
//        // 商品导出模板
//        String[] headerCn = {"商品中文名称（必填*）", "商品英文名称", "商品类型（必填*）", "商品类别（必填*）", "平台分类（必填*）", "店铺分类（必填*）","商品图片","商品视频", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择（当商品类别为实物商品且类型为普通商品时必填*）", "固定运费（当运费选择为固定运费时必填*）","商品状态",
//                "核销次数(虚拟商品必填*)","多次核销次数(多次核销选项，不填默认无限次)", "核销有效期(有效期，x天，(虚拟商品必填*))", "核销开始时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
//                "可以退款(虚拟商品必填*)", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
//                "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积","库存预警"};
//        String[] headerEn = {"Chinese name(Required*)", "English name", "Product Type(Required*)", "Product Category(Required*)", "platform classification(Required*)", "store classification(Required*)", "product pic","product video","Chinese selling point",
//                "English selling point", "delivery method", "Freight selection(Required when the product category is physical goods and the product type is general goods*)","Shipping costs(Required when shipping selected as fixed*)", "product status",
//                "Checkout Count(Virtual goods required*)","The number of multiple write-offs (multiple write-off options, do not fill in the default unlimited)", "Write-off validity(Valid period, x days(Virtual goods required*))", "Checkout Start Time(If the validity period of the write-off is customized, it is required)", "Checkout End Time(If the validity period of the write-off is customized, it is required)",
//                "Refundable(Virtual goods required*)", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
//                "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
//                "Inventory", "commodity code", "commodity weight", "commodity volume","stockWarning"};
//        List<String> headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
//        Sheet sheet = getSheet(writer, shopId);
//
//        writer.merge(headerList.size() - 1, "商品信息整理");
//        writer.writeRow(headerList);
//        setComment(sheet.getRow(1).getCell(6),"1、选填，请填写图片在图片服务器的URL地址;\n" +
//                "2、最多可上传9张，不同图片请以“,”隔开\n" +
//                "3、归属于同一SPU的SKU， 只需填写第一条SKU的商品图片;",sheet);
//
//        setComment(sheet.getRow(1).getCell(7),"1、选填，请填写视频在视频服务器的URL地址;\n" +
//                "2、只能添加一个视频链接;\n" +
//                "3、 归属同一SPU的SKU,只需填写第一条SKU的商品视频;\n" +
//                "4、 视频仅支持mp4格式,最大限制50M;",sheet);
// TODO -m
        try (Workbook workbook = writer.getWorkbook()) {
            Sheet sheet = writer.getSheet();
            setComment(sheet.getRow(1).getCell(langConfig.getLangItemList().size() + 4),"1、选填，请填写图片在图片服务器的URL地址;\n" +
                    "2、最多可上传9张，不同图片请以“,”隔开" ,sheet);

            setComment(sheet.getRow(1).getCell(langConfig.getLangItemList().size() + 5),"1、选填，请填写视频在视频服务器的URL地址;\n" +
                    "2、只能添加一个视频链接;\n" +
                    "3、 视频仅支持mp4格式,最大限制50M;",sheet);
            dropDownList(shopId, sheet, workbook, langConfig);
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("Exception:", e);
        }
    }

    @Override
    public Object parseFile(MultipartFile file, Long shopId, Long employeeId, List<Long> prodIds, boolean isSameCity) throws Exception {
        Integer lang = I18nMessage.getDbLang();
        try (InputStream inputStream = file.getInputStream();
             Workbook workBook = WorkbookFactory.create(inputStream)) {
            //读取工作表
            Sheet sheet = workBook.getSheetAt(0);
            int rowNumber = sheet.getPhysicalNumberOfRows();
            //校验是否填写内容
            if (rowNumber <= Constant.EXCEL_BEGIN_ROW) {
                // 文件无内容
                throw new YamiShopBindException("yami.document.no.content");
            }
            // * 2.1不是Excel文件，直接返回提示信息
            if (!(file.getOriginalFilename().contains(Constant.XLS_FILE) || file.getOriginalFilename().contains(Constant.XLSX_FILE))) {
                // 格式错误
                throw new YamiShopBindException("yami.file.error");
            }

            // 获取运费模板map
            List<TransportParam> transportParams = new ArrayList<>();
            applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
            Map<String, Long> transportMap = transportParams.stream().collect(Collectors.toMap(TransportParam::getTransName, TransportParam::getTransportId, (key1, key2) -> key2));
            transportMap.put(DeliveryTemplateType.FREE_SHIPPING.getName(), DeliveryTemplateType.FREE_SHIPPING.getValue());
            transportMap.put(DeliveryTemplateType.FREIGHT.getName(), DeliveryTemplateType.FREIGHT.getValue());

            //获取已有的，未被删除的商品编码
            List<Product> dbProducts = productMapper.selectList(new LambdaQueryWrapper<Product>().eq(Product::getShopId, shopId)
                    .ne(Product::getStatus, -1));
            List<Long> dbProdIds = dbProducts.stream().map(Product::getProdId).collect(Collectors.toList());
            List<String> partyCodes = skuService.listSkuByProdIds(dbProdIds, null);
            HashSet<String> partyCodeSet = new HashSet<>();
            // 获取商品审核开关
            SysProdConfig sysProdConfig = sysConfigService.getSysConfigObject(Constant.PROD_SWITCH_CONFIG, SysProdConfig.class);
            Boolean prodAudit = sysProdConfig.getProdAudit();
            List<ProductParam> products = new ArrayList<>();
            List<String> errorList = new ArrayList<>();
            Set<Integer> errorSet = new HashSet<>();
            List<Sku> skuList = new ArrayList<>();
            int insertRows = loadProdData(shopId, isSameCity, sheet, rowNumber, transportMap, partyCodes, partyCodeSet, prodAudit, products, errorList, errorSet, skuList);
//            int totalRows = (rowNumber - Constant.EXCEL_BEGIN_ROW);
            int totalRows = products.size();
            String response;
            //过滤删除状态的商品
            products = products.stream().filter(prod -> !prod.getStatus().equals(ProdStatusEnums.DELETE.getValue())).collect(Collectors.toList());
            //不为空批量导
            if (CollUtil.isNotEmpty(products)) {
                int errorCount = errorList.size();
                saveImportProd(prodIds, products, errorList, errorSet, employeeId);
                errorCount = errorList.size() - errorCount;
                response = "检查到" + totalRows + "条商品，成功导入" +
                        (products.size() - errorCount) + "条商品信息！错误" + errorSet.size() + "条商品信息！" +
                        (CollUtil.isNotEmpty(errorList) ? errorList.toString() : "");
                return response;
            }
            response = Objects.equals(lang, LanguageEnum.LANGUAGE_ZH_CN.getLang()) ? "数据错误!导入0条" : "Data error! Import 0 items";
            if (CollUtil.isNotEmpty(errorList)) {
                response = response + errorList.toString();
            }
            return response;
        } catch (Exception e) {
            return e.getMessage();
        }
    }

    @Override
    public void prodEffectExport(HttpServletResponse response, ProdEffectParam param, Integer lang) {

        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 导出模板
        String[] header = {"店铺名称", "商品名称", "商品价格", "商品状态", "曝光次数", "曝光人数", "加购人数", "加购件数", "下单人数", "支付人数", "下单件数", "支付件数",
                "下单金额", "支付金额", "单品转化率", "申请退款订单数", "申请退款人数", "成功退款订单数", "成功退款人数", "成功退款金额", "退款率"};
        List<String> headerList = Arrays.asList(header);
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 40 * 256);
        sheet.setColumnWidth(1, 40 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        for (int i = 3; i <= headerList.size(); i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }

        writer.merge(headerList.size() - 1, "商品洞察");
        writer.writeRow(headerList);
        if (Objects.equals(1, param.getGroup())) {
            return;
        }
        IPage<ProdEffectRespParam> pageProdEffect = productService.pageProdEffect(new PageParam<>(), param, I18nMessage.getDbLang(), true);
        if (CollUtil.isEmpty(pageProdEffect.getRecords())) {
            PoiExcelUtil.writeExcel(response, writer);
            return;
        }
        try {
            int row = 1;
            // 写入商品洞察信息
            for (ProdEffectRespParam prod : pageProdEffect.getRecords()) {
                int col = -1;
                PoiExcelUtil.mergeIfNeed(writer, ++row, row, ++col, col, prod.getShopName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPrice());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdStatus());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExpose());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExposePersonNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCartPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCart());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getSingleProdRate());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessRate());
            }
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("商品洞察导出:", e);
            throw new YamiShopBindException(e.getMessage());
        }
    }

    @Override
    public void dropDownList(Long shopId, Sheet sheet, Workbook workbook, LangConfig langConfig) {
        int size = langConfig.getLangItemList().size();
        int prodTypeIndex =  size;
        int moldIndex = 1 + size;
        // 平台分类的索引
        int platformCategoryIndex = 2 + size;
        // 店铺分类的索引
        int shopCategoryIndex = 3 + size;
        int dvyTypeIndex = 6 + size * 2;
        int transportIndex = 7 + size * 2;
        int statusIndex = 9 + size * 2;
        int writeOffTypeIndex = 10 + size * 2;
        int isRefundIndex = 15 + size * 2;

        // shopId: null-导出商城的商品,比店铺模板多了一个店铺名称，所以对应属性的索引要+1
        if (Objects.isNull(shopId)) {
            prodTypeIndex++;
            moldIndex++;
            dvyTypeIndex++;
            transportIndex++;
            statusIndex++;
            writeOffTypeIndex++;
            isRefundIndex++;
        }


        if (Objects.isNull(shopId)) {
            shopId = Constant.PLATFORM_SHOP_ID;
        }
        // 限制类型
        String[] prodType = {"普通商品", "活动商品"};
        PoiExcelUtil.createDropDownList(sheet, prodType, 2, 50000, prodTypeIndex, prodTypeIndex);
        // 限制类别
        String[] prodCategory = {"实物商品", "虚拟商品"};
        PoiExcelUtil.createDropDownList(sheet, prodCategory, 2, 50000, moldIndex, moldIndex);

        categoryDropDownList(shopId, sheet, workbook, platformCategoryIndex, shopCategoryIndex);

        // 限制物流方式
        String[] dvyTypes = {"用户自提", "同城配送", "用户自提且同城配送"};
        PoiExcelUtil.createDropDownList(sheet, dvyTypes, 2, 50000, dvyTypeIndex, dvyTypeIndex);

        // 组装运费模板
        List<TransportParam> transportParams = new ArrayList<>();
        applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
        List<String> transportNameList = new ArrayList<>();
        transportNameList.add(DeliveryTemplateType.FREE_SHIPPING.getName());
        transportNameList.add(DeliveryTemplateType.FREIGHT.getName());
        if (CollUtil.isNotEmpty(transportParams)) {
            transportParams.forEach(transportParam -> transportNameList.add(transportParam.getTransName()));
        }
        PoiExcelUtil.createDropDownList(workbook, sheet, transportNameList, 2, 50000, 11, 11);

        String[] statusStr = {"下架", "上架"};
        PoiExcelUtil.createDropDownList(workbook, sheet, transportNameList, 2, 50000, transportIndex, transportIndex);

        // 限制核销次数 -1.多次核销 0.无需核销 1.单次核销
        String[] writeOffType = {"多次核销", "无需核销", "单次核销"};
        PoiExcelUtil.createDropDownList(sheet, writeOffType, 2, 50000, writeOffTypeIndex, writeOffTypeIndex);

        // 核销有效期 -1.长期有效 0.自定义 x.x天内有效
//        String[] writeOffTime = {"长期有效","自定义","x天内有效"};
//        createDropDownList(sheet,writeOffTime,2,50000,11,11);
        // 是否可以退款 1.可以 0.不可以
        String[] isRefund = {"是", "否"};
        PoiExcelUtil.createDropDownList(sheet, isRefund, 2, 50000, isRefundIndex, isRefundIndex);
    }

    private void categoryDropDownList(Long shopId, Sheet sheet, Workbook workbook, int platformCategoryIndex, int shopCategoryIndex) {
        if (Objects.equals(shopId, Constant.PLATFORM_SHOP_ID)) {
            return;
        }
        // 限制商家分类和平台分类
        List<Category> platformCategoryList = this.listPlatformCategory(shopId);

        if (CollUtil.isNotEmpty(platformCategoryList)) {
            String[] categoryName = new String[platformCategoryList.size()];
            for (int i = 0; i < platformCategoryList.size(); i++) {
                categoryName[i] = platformCategoryList.get(i).getCategoryName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, platformCategoryIndex, platformCategoryIndex);
        }
        List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
        if (CollUtil.isNotEmpty(shopCategories)) {
            String[] categoryName = new String[shopCategories.size()];
            langManager.getCategoryLang(shopCategories);
            for (int i = 0; i < shopCategories.size(); i++) {
                categoryName[i] = shopCategories.get(i).getCategoryName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, shopCategoryIndex, shopCategoryIndex);
        }
    }

    @Override
    public List<Category> listPlatformCategory(Long shopId) {
        List<Category> categories = categoryShopService.listSigningCategoryAndParent(shopId);
        categories.sort(Comparator.comparing(Category::getParentId));
        List<Category> categoryList = new ArrayList<>();
        if (Objects.isNull(categories)) {
            return categoryList;
        }
        Map<Long, Category> categoryMap = categories.stream()
                .filter(category -> !Objects.equals(category.getGrade(), CategoryLevel.THIRD.value()))
                .collect(Collectors.toMap(Category::getCategoryId, c -> c));
        for (Category category : categories) {
            // 一二级分类不用处理
            if(!Objects.equals(category.getGrade(), CategoryLevel.THIRD.value())) {
                continue;
            }
            Category secondCategory = categoryMap.get(category.getParentId());
            if (Objects.isNull(secondCategory)) {
                continue;
            }
            Category firstCategory = categoryMap.get(secondCategory.getParentId());
            if (Objects.isNull(firstCategory)) {
                continue;
            }
            String categoryName = firstCategory.getCategoryName() + "/" + secondCategory.getCategoryName() + "/" +category.getCategoryName();
            category.setCategoryName(categoryName);
            categoryList.add(category);
        }
        return categoryList;
    }

    public void saveImportProd(List<Long> prodIds, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, Long employeeId) {
        int count = 0;
        for (ProductParam productParam : products) {
            count++;
            try {
                // 有事务，发生错误会回滚
                productParam.setEmployeeId(employeeId);
                productService.saveProduct(productParam);
                prodIds.add(productParam.getProdId());
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + count + "行数据有错误:" + e.getMessage());
                errorSet.add(count);
            }
        }
    }

    private int loadProdData(Long shopId, boolean isSameCity, Sheet sheet, int rowNumber, Map<String, Long> transportMap, List<String> partyCodes, HashSet<String> partyCodeSet, Boolean prodAudit, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, List<Sku> skuList) {
        // 获取平台分类map
        List<Category> platformCategoryList = this.listPlatformCategory(shopId);
        if (CollUtil.isEmpty(platformCategoryList)) {
            // 当前店铺没有签约的平台分类，请先签约分类
            throw new YamiShopBindException("yami.shop.not.category");
        }

        // 如果重复的key取最后一个的值
        Map<String, Long> categoryMap = platformCategoryList.stream().collect(Collectors.toMap(Category::getCategoryName, Category::getCategoryId, (key1, key2) -> key2));
        // 获取店铺分类map
        List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
        Map<String, Long> shopCategoryMap = getCategoryNameMap(shopCategories);

        LangConfig langConfig = langManager.getLangConfig();
        Integer[] langArray = getProdExcelLang(sheet);
        Date date = new Date();
        Integer totalStocks = 0;
        double price = 0.0;
        double oriPrice = 0.0;
        int insertRows = 0;
        ProductParam product = new ProductParam();
        int mergerRow = 0;
        //循环读取每一行数据并校验
        for (int i = 2; i < rowNumber; i++) {
            try {
                //读取行
                Row row = sheet.getRow(i);
                int cell = -1;
                if(i >= 2 && PoiExcelUtil.isMergedRegion(sheet, i, 0) && !PoiExcelUtil.isMergedRegion(sheet, i-1, 0)){
                    mergerRow = i;
                }
                Map<Integer, ProdLangParam> prodLangMap = new HashMap<>();
                for (int l = 0; l < langArray.length; l++) {
                    Integer lang = langArray[l];
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String prodName = PoiExcelUtil.getRowValue(row, ++cell, false);
                    if (StrUtil.isBlank(prodName)) {
                        continue;
                    }
                    ProdLangParam prodLang = prodLangMap.get(lang);
                    if (Objects.isNull(prodLang)) {
                        prodLang = new ProdLangParam();
                        prodLangMap.put(lang, prodLang);
                    }
                    prodLang.setLang(lang);
                    prodLang.setProdName(prodName);
                }

                String prodType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String prodCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String category = PoiExcelUtil.getRowValue(row, ++cell, false);
                String shopCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String pic = PoiExcelUtil.getRowValue(row, ++cell, false);
                String video = PoiExcelUtil.getRowValue(row, ++cell, false);
                for (int l = 0; l < langArray.length; l++) {
                    Integer lang = langArray[l];
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String brief = PoiExcelUtil.getRowValue(row, ++cell, false);
                    ProdLangParam prodLang = prodLangMap.get(lang);
                    if (Objects.isNull(prodLang)) {
                        continue;
                    }
                    prodLang.setBrief(brief);
                }

                String dvyType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String transportName = PoiExcelUtil.getRowValue(row, ++cell, false);
                String freightAmount = PoiExcelUtil.getRowValue(row, ++cell, true);
                String statusStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffNum = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffMultipleCount = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffTime = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffStart = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffend = PoiExcelUtil.getRowValue(row, ++cell, false);
                String isRefund = PoiExcelUtil.getRowValue(row, ++cell, false);
                String virtualRemark = PoiExcelUtil.getRowValue(row, ++cell, false);
//                String prodParameterStr = PoiExcelUtil.getRowValue(row, ++cell, false);

                Map<Integer, String> prodParameterLangMap = new HashMap<>();
                for (int l = 0; l < langArray.length; l++) {
                    Integer lang = langArray[l];
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String prodParameterStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                    prodParameterLangMap.put(lang, prodParameterStr);
                }

                if (prodLangMap.containsKey(langConfig.getLang()) || !PoiExcelUtil.isMergedRegion(sheet, i, 0)) {
                    ProdLangParam prodLang = prodLangMap.get(langConfig.getLang());
                    if (StrUtil.isBlank(prodLang.getProdName()) && StrUtil.isBlank(category) &&
                            StrUtil.isBlank(shopCategory) && Objects.isNull(transportMap.get(transportName))) {
                        continue;
                    }
                    if (StrUtil.isBlank(prodLang.getProdName())) {
                        // 默认商品名称不能为空
                        throw new YamiShopBindException("[" +I18nMessage.getMessage("yami.excel.prod.name") + "-" + langConfig.getName() + "] " + I18nMessage.getMessage("yami.not.null"));
                    }
                    if (StrUtil.isBlank(prodType)) {
                        prodType = ProdType.PROD_TYPE_NORMAL.desc();
                    }
                    if (StrUtil.isBlank(prodCategory)) {
                        // 商品类别不能为空
                        throw new YamiShopBindException("商品类别不能为空");
                    }
                    if(StrUtil.isBlank(shopCategory)||!shopCategoryMap.containsKey(shopCategory)){
                        throw new YamiShopBindException("店铺分类为空或不存在");
                    }
                    skuList = new ArrayList<>();
                    product = new ProductParam();
                    this.newProdInfo(shopId, isSameCity, categoryMap, shopCategoryMap, transportMap, prodAudit, products, product, prodLangMap, prodType, prodCategory, category, shopCategory,pic,video, dvyType, transportName,freightAmount, statusStr, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark, prodParameterLangMap);
                    totalStocks = 0;
                    oriPrice = 0.0;
                    price = 0.0;
                    insertRows++;
                }
                boolean mergedRegion = PoiExcelUtil.isMergedRegion(sheet, i, 0);
                Map<Integer, SkuLang> skuLangMap = new HashMap<>();
                prodLangMap = product.getProdLangList().stream().collect(Collectors.toMap(ProdLangParam::getLang, p->p));
                // sku名称
                for (int l = 0; l < langArray.length; l++) {
                    Integer lang = langArray[l];
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String skuName = PoiExcelUtil.getRowValue(row, ++cell, false);
                    if (!Objects.equals(lang, langConfig.getLang()) && StrUtil.isBlank(skuName)) {
                        continue;
                    }
                    SkuLang skuLang = skuLangMap.get(lang);
                    if (Objects.isNull(skuLang)) {
                        skuLang = new SkuLang();
                        ProdLangParam prodLang = prodLangMap.get(lang);
                        skuLang.setProdName(prodLang.getProdName());
                        skuLangMap.put(lang, skuLang);
                    }
                    if (mergedRegion && Objects.equals(lang, langConfig.getLang()) && StrUtil.isBlank(skuName)) {
                        throw new YamiShopBindException("[" +I18nMessage.getMessage("yami.excel.prod.sku.name") + "-" + langConfig.getName() + "] " + I18nMessage.getMessage("yami.product.sku.name.not.null"));
                    }
                    skuLang.setLang(lang);
                    skuLang.setSkuName(skuName);
                }

                for (int l = 0; l < langArray.length; l++) {
                    Integer lang = langArray[l];
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String properties = PoiExcelUtil.getRowValue(row, ++cell, false);
                    SkuLang skuLang = skuLangMap.get(lang);
                    if (Objects.isNull(skuLang)) {
                        continue;
                    }
                    //判断规格名是否重复
                    String checkProperties = checkProperties(properties, product);
                    if (mergedRegion && Objects.equals(lang, langConfig.getLang()) && StrUtil.isBlank(checkProperties)) {
                        throw new YamiShopBindException("[" +I18nMessage.getMessage("yami.excel.sales.attribute") + "-" + langConfig.getName() + "] " + I18nMessage.getMessage("yami.product.sku.properties.not.null"));
                    }
                    skuLang.setProperties(properties);
                    // 没有规格时，不需要插入sku名称
                    if (StrUtil.isBlank(properties)) {
                        skuLang.setSkuName("");
                    }
                }
//                String properties = PoiExcelUtil.getRowValue(row, ++cell, false);
                double skuOriPrice = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
//                //判断规格名是否重复
//                String checkProperties = checkProperties(properties, product);
//                //多规格 sku名称不能为空
//                if(PoiExcelUtil.isMergedRegion(sheet, i, 0) && (StrUtil.isBlank(skuProdNameCn) ||  StrUtil.isBlank(checkProperties))){
//                    throw new YamiShopBindException("多规格商品的商品规格和销售属性都不能为空");
//                }


                if (Objects.isNull(skuOriPrice) || skuOriPrice < Constant.MIN_PRODUCT_AMOUNT) {
                    skuOriPrice = Constant.MIN_PRODUCT_AMOUNT;
                }
                double skuPrice = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                if (Objects.isNull(skuPrice) || skuPrice < Constant.MIN_PRODUCT_AMOUNT) {
                    skuPrice = Constant.MIN_PRODUCT_AMOUNT;
                }
                // 跳过积分价格的字段 --> 改：Excel没有该列
                Integer stocks = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true)).intValue();
                String partyCode = PoiExcelUtil.getRowValue(row, ++cell, false);
                if (StrUtil.isBlank(partyCode) || partyCodes.contains(partyCode) || partyCodeSet.contains(partyCode)) {
                    partyCode = StringUtils.join("RM", String.valueOf(snowflake.nextId()));
                }
                partyCodeSet.add(partyCode);
                double weight = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                double volume = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                oriPrice = Objects.equals(oriPrice, 0.0) ? skuOriPrice : oriPrice;
                price = Objects.equals(price, 0.0) ? skuPrice : price;
                Integer stockWarning = Integer.parseInt(PoiExcelUtil.getRowValue(row, ++cell, true));
                if(Objects.nonNull(stockWarning)&&stockWarning<0){
                    throw new YamiShopBindException("库存预警不能小于0");
                }
                Sku sku = this.getSku(date, skuOriPrice, skuPrice, stocks, partyCode, weight, volume,stockWarning);
                sku.setSkuLangList(new ArrayList<>(skuLangMap.values()));
                skuList.add(sku);
                // 如果prodName为空并且为合并行则为一个prod
                totalStocks += stocks;
                if (price > skuPrice) {
                    oriPrice = skuOriPrice;
                    price = skuPrice;
                }
                product.setOriPrice(oriPrice);
                product.setScorePrice(0);
                product.setPrice(price);
                product.setTotalStocks(totalStocks);
                product.setSkuList(skuList);
            } catch (NumberFormatException numberFormatException) {
                errorList.add("第" + (i + 1) + "行数据有错误：" + "输入内容[" + numberFormatException.getMessage().substring(18) + "]格式错误");
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + (i + 1) + "行数据有错误：" + e.getMessage());
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
            }
        }
        List<ProductParam> wrongList = products.stream().filter(prod -> CollUtil.isNotEmpty(prod.getSkuList()) && prod.getSkuList().size() > 100).collect(Collectors.toList());
        if (CollUtil.isNotEmpty(wrongList)) {
            // 检测到存在超过100组规格的商品，请修改后重新导入
            throw new YamiShopBindException("yami.exprot.prod.sku.error");
        }
        return insertRows;
    }

    @NotNull
    private Map<String, Long> getCategoryNameMap(List<Category> categories) {
        List<Long> categoryIds = categories.stream().map(Category::getCategoryId).collect(Collectors.toList());
        List<CategoryLang> list = categoryLangService.list(new LambdaQueryWrapper<CategoryLang>().in(CategoryLang::getCategoryId, categoryIds));
        // 如果重复的key取最后一个的值
        Map<String, Long> categoryMap = list.stream().collect(Collectors.toMap(CategoryLang::getCategoryName, CategoryLang::getCategoryId, (key1, key2) -> key2));
        return categoryMap;
    }

    private Integer[] getProdExcelLang(Sheet sheet) {
        LangConfig langConfig = langManager.getLangConfig();
        Map<String, Integer> langMap = langConfig.getLangItemList().stream().collect(Collectors.toMap(LangItemConfig::getName, LangItemConfig::getLang));
        List<Integer> list = new ArrayList<>();
        Row row = sheet.getRow(1);
        int cell = -1;
        boolean hasNext = false;
        boolean defaultLang = false;
        do {
            hasNext = false;
            String prodName = PoiExcelUtil.getRowValue(row, ++cell, false);
            if (StrUtil.isBlank(prodName)) {
                list.add(-1);
                continue;
            }
            String[] split = prodName.split("-");
            if (ArrayUtil.isEmpty(split) || split.length < 2) {
                break;
            }
            String language = split[1];
            if (StrUtil.contains(language, "（")) {
                String[] tagArray = language.split("（");
                if (ArrayUtil.isNotEmpty(tagArray) || tagArray.length == 2) {
                    language = tagArray[0];
                }
            }
            Integer lang = langMap.get(language);
            if (Objects.isNull(lang)) {
                list.add(-1);
                continue;
            }
            if (Objects.equals(lang, langConfig.getLang())) {
                defaultLang = true;
            }
            list.add(lang);
            hasNext = true;
        } while (hasNext);
        if (!defaultLang) {
            throw new YamiShopBindException("商品模板中没有默认语言的数据");
        }
        if (CollUtil.isEmpty(list)) {
            throw new YamiShopBindException("模板错误，请下载最新的导入模板");
        }
        return (Integer[]) list.toArray(new Integer[list.size()]);
    }

    public String checkProperties(String properties, ProductParam product) {
        if (StrUtil.isBlank(properties)) {
            return properties;
        }
        StringBuilder stringBuilder = new StringBuilder();
        if (!hasSemicolon(properties)) {
            if (properties.split(COLON).length == 0) {
                return "";
            }
            if (properties.split(COLON, 2).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            return stringBuilder.append(properties).toString();
        }
        Set<String> propertyKeySet = new HashSet<>();
        String[] property = properties.split(SEMICOLON);
        for (int i = 0; i < property.length; i++) {
            String prop = property[i];
            if (prop.split(COLON, 2).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            String key = prop.split(COLON, 2)[0].trim();
            String value = prop.split(COLON, 2)[1].trim();
            if (propertyKeySet.contains(key)) {
                product.setStatus(-1);
                //规格名重复
                String message = I18nMessage.getMessage("yami.sku.name");
                String error = I18nMessage.getMessage("yami.is.duplicate");
                throw new YamiShopBindException(message + "[" + key + "]" + error);
            }
            if (StrUtil.isNotBlank(key)) {
                propertyKeySet.add(key);
            }
            if (StrUtil.isNotBlank(value) && i != property.length - 1) {
                stringBuilder.append(prop).append(";");
            } else if (i == property.length - 1) {
                stringBuilder.append(prop);
            }
        }
        if (propertyKeySet.isEmpty()) {
            return "";
        }
        return stringBuilder.toString();
    }

    private Sku getSku(Date date, double skuOriPrice, double skuPrice, Integer stocks, String partyCode, double weight, double volume,int stockWarning) {
        Sku sku = new Sku();
//        sku.setSkuName(skuProdNameCn);
//        sku.setSkuNameEn(skuProdNameEn);
//        sku.setProdNameCn(prodName);
//        sku.setProdNameEn(prodNameEn);
//        sku.setProperties(properties);
//        sku.setPropertiesEn(properties);
//        // 没有规格时，不需要插入sku名称
//        if (StrUtil.isBlank(properties)) {
//            sku.setSkuName("");
////            sku.setSkuNameEn("");
//        }
        sku.setOriPrice(skuOriPrice);
        sku.setPrice(skuPrice);
        sku.setStocks(Math.abs(stocks));
        sku.setSkuScore(0L);
        sku.setPartyCode(partyCode);
        sku.setVolume(Math.abs(volume));
        sku.setUpdateTime(date);
        sku.setWeight(Math.abs(weight));
        sku.setVersion(1);
        sku.setIsDelete(0);
        sku.setStatus(1);
        sku.setStockWarning(stockWarning);
        return sku;
    }

    private void newProdInfo(Long shopId, boolean isSameCity, Map<String, Long> categoryMap, Map<String, Long> shopCategoryMap, Map<String, Long> transportMap, Boolean prodAudit, List<ProductParam> products, ProductParam product, Map<Integer, ProdLangParam> prodLangMap, String prodType, String prodCategory, String category, String shopCategory,String pic,String video, String dvyType, String transportName,String freightAmount, String statusStr, String writeOffNum, String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffend, String isRefund, String virtualRemark, Map<Integer, String> prodParameterLangMap) {
        if (StrUtil.isBlank(category)) {
            // 平台分类不能为空
            throw new YamiShopBindException("yami.platform.category.not.blank");
        }
        if (StrUtil.isBlank(shopCategory)) {
            // 店铺分类不能为空
            throw new YamiShopBindException("yami.prod.shop.category.not.blank");
        }
        // 保存语言信息
//        product.setProdNameCn(prodName);
//        product.setProdNameEn(prodNameEn);
//        product.setBriefCn(brief);
//        product.setBriefEn(briefEn);

        product.setShopId(shopId);
        product.setProdLangList(new ArrayList<>(prodLangMap.values()));
        product.setCategoryId(categoryMap.get(category));
        if (Objects.isNull(product.getCategoryId())) {
            throw new YamiShopBindException("分类" + category + "的签约关系已失效，无法使用该分类创建商品");
        }
        product.setShopCategoryId(shopCategoryMap.get(shopCategory));
        if(StrUtil.isNotBlank(pic)){
            String[] split = pic.split(",");
            checkPic(split);
            product.setPic(pic.split(",")[0]);
            product.setImgs(pic);
        }
        if(StrUtil.isNotBlank(video)){
            checkVideo(video);
            product.setVideo(video);
        }
        product.setDeliveryModeVo(loadDvyType(dvyType, isSameCity));
        product.setDeliveryTemplateId(transportMap.get(transportName));
        if (Objects.equals(transportMap.get(transportName), DeliveryTemplateType.FREIGHT.getValue())) {
            double deliveryAmount = Double.parseDouble(freightAmount);
            if (deliveryAmount < Constant.MIN_PRODUCT_AMOUNT) {
                deliveryAmount = Constant.MIN_PRODUCT_AMOUNT;
            }
            product.setDeliveryAmount(deliveryAmount);
        }
        product.setStatus(Objects.equals(statusStr, "下架") ? ProdStatusEnums.SHOP_OFFLINE.getValue() : ProdStatusEnums.NORMAL.getValue());
        // 如果商品审核true，且是商品上架状态的则为待审核状态
        if (BooleanUtil.isTrue(prodAudit) && Objects.equals(product.getStatus(), ProdStatusEnums.NORMAL.getValue())) {
            product.setStatus(ProdStatusEnums.AUDIT.getValue());
        }
        if (StrUtil.equals(ProdType.PROD_TYPE_NORMAL.desc(), prodType)) {
            if (Objects.isNull(transportMap.get(transportName)) && !StrUtil.equals("虚拟商品", prodCategory)) {
                // 运费模板不存在
                throw new YamiShopBindException("yami.prod.transport.not.exist");
            }
            product.setProdType(ProdType.PROD_TYPE_NORMAL.value());
        } else {
            product.setProdType(ProdType.PROD_TYPE_ACTIVE.value());
        }
        if (StrUtil.equals("实物商品", prodCategory)) {
            product.setMold(0);
        } else {
            // 处理下虚拟商品信息
            handleVirtualProdInfo(product, prodType, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark);
        }
        product.setProdParameterList(parseParameter(prodParameterLangMap));
        product.setContent("<p></p>");
        products.add(product);
    }

    private void checkVideo(String video) {
        try {
            URL url = new URL(video);
            if(!("video/mp4".equals(url.openConnection().getHeaderField("content-type"))||video.endsWith(".map4"))){
                throw new YamiShopBindException("yami.prod.video.format.error");
            }
        }  catch (IOException e) {
            log.error("视频格式判断出错:{}",e.getMessage());
        }
        int length = getFileLength(video);
        int MB = 1024 * 1024;//定义MB的计算常量
        DecimalFormat df = new DecimalFormat("0.00");
        float lengthByMB = Float.parseFloat(df.format(length / (float) MB));
        if(lengthByMB>50){
            throw new YamiShopBindException("yami.prod.video.too.large.error");
        }
    }

    private int getFileLength(String url1) {
        int length=0;
        URL url;
        try {
            url = new  URL(url1);
            HttpURLConnection urlcon=(HttpURLConnection)url.openConnection();
            //根据响应获取文件大小
            length=urlcon.getContentLength();
            urlcon.disconnect();
        } catch (MalformedURLException e) {
            e.getMessage();
        } catch (IOException e) {
            e.getMessage();
        }
        return length;
    }

    private void checkPic(String[] split) {
        if(split.length>9){
            throw new YamiShopBindException("yami.prod.pic.limit");
        }
        for (String s : split) {
            if(!isImgUrl(s)){
                throw new YamiShopBindException("yami.prod.pic.format.error");
            }
        }
    }

    private boolean isImgUrl(String url) {
        if (url == null || url.trim().length() == 0) {
            return false;
        }
        String suffix = url.substring(url.lastIndexOf(".")+1);
        try {
            return IMG_URL_suffix.matcher(suffix).matches()||IMG_URL.matcher(new URL(url).openConnection().getHeaderField("content-type")).matches();
        }  catch (IOException e) {
            log.error("图片判断出错:{}",e.getMessage());
        }
        return false;
    }

    private Product.DeliveryModeVO loadDvyType(String dvyType, Boolean isSameCity) {
        Product.DeliveryModeVO mode = new Product.DeliveryModeVO();
        mode.setHasShopDelivery(true);
        mode.setHasUserPickUp(false);
        mode.setHasCityDelivery(false);
        if (Objects.isNull(dvyType)) {
            return mode;
        }
        if (dvyType.contains("用户自提且同城配送")) {
            mode.setHasUserPickUp(true);
            mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
            return mode;
        }

        if (dvyType.contains("用户自提")) {
            mode.setHasUserPickUp(true);
        }

        if (dvyType.contains("同城配送")) {
            mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
        }
        return mode;
    }

    private void handleVirtualProdInfo(ProductParam product, String prodType, String writeOffNum,String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffEnd, String isRefund, String virtualRemark) {
        if (StrUtil.equals(ProdType.PROD_TYPE_ACTIVE.desc(), prodType)) {
            throw new YamiShopBindException("虚拟商品不能为活动商品");
        }
        if (StrUtil.isBlank(writeOffNum)) {
            throw new YamiShopBindException("虚拟商品的核销次数不能为空");
        }
        String num = StrUtil.equals(writeOffNum, "多次核销") ? "-1" : StrUtil.equals(writeOffNum, "单次核销") ? "1" : "0";
        if (!"0".equals(num) && StrUtil.isBlank(writeOffTime)) {
            throw new YamiShopBindException("虚拟商品的核销有效期不能为空");
        }
        // 多次核销次数
        int count;
        try {
            count = Math.max(Integer.parseInt(writeOffMultipleCount), 2);
        } catch (NumberFormatException e) {
            count = -1;
        }
        if ("-1".equals(num)) {
            product.setWriteOffMultipleCount(count);
        }
        boolean validWriteOffTime = StrUtil.equals(writeOffTime, "自定义") && (StrUtil.isBlank(writeOffStart) || StrUtil.isBlank(writeOffEnd));
        if (validWriteOffTime) {
            throw new YamiShopBindException("虚拟商品的核销有效期不能为空");
        }
        if (StrUtil.isBlank(isRefund)) {
            throw new YamiShopBindException("虚拟商品是否可以退款不能为空");
        }
        if (StrUtil.isBlank(writeOffTime)) {
            product.setWriteOffTime(null);
        } else {
            if(!"当天有效".equals(writeOffTime)){
                writeOffTime = writeOffTime.replace("天", "");
            }
            int time = StrUtil.equals(writeOffTime, "长期有效") ? -1 : StrUtil.equals(writeOffTime, "当天有效") ? 1 : StrUtil.equals(writeOffTime, "自定义") ? 0 : Integer.parseInt(writeOffTime);
            product.setWriteOffTime(time);
        }
        product.setWriteOffNum(num);

        if (StrUtil.isNotBlank(writeOffStart)) {
            product.setWriteOffStart(DateUtil.parse(writeOffStart, "yyyy-MM-dd HH:mm:ss"));
        }
        if (StrUtil.isNotBlank(writeOffEnd)) {
            product.setWriteOffEnd(DateUtil.parse(writeOffEnd, "yyyy-MM-dd HH:mm:ss"));
        }
        product.setIsRefund(StrUtil.equals("是", isRefund) ? 1 : 0);
        if(StrUtil.isNotBlank(virtualRemark)){
            if (!JSONValidator.Type.Array.equals(JSONValidator.from(virtualRemark).getType())) {
                throw new YamiShopBindException("yami.prod.import.virtualRemark.notValid");
            }
            List<Map> mapList = JSONObject.parseArray(virtualRemark, Map.class);
            if(mapList.size() > 10){
                List newList = mapList.subList(0, 10);
                virtualRemark = JSON.toJSONString(newList);
            }
        }
        product.setVirtualRemark(StrUtil.isBlank(virtualRemark) ? null : virtualRemark);
        product.setMold(1);
    }

    public List<ProdParameter> parseParameter(String prodParameterStr, ProductParam product) {
        if (StrUtil.isBlank(prodParameterStr)) {
            return null;
        }
        if (!hasSemicolon(prodParameterStr)) {
            int length = prodParameterStr.split(COLON).length;
            if (length == 0) {
                return null;
            }
            if (length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
        }
        // 商品参数名，值都可以相同，不做校验
        String[] prodParameters = prodParameterStr.split(SEMICOLON);
        List<ProdParameter> prodParameterList = new ArrayList<>();
        String key, value;
        for (String prodParameter : prodParameters) {
            if (prodParameter.split(COLON).length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            key = prodParameter.split(COLON)[0].trim();
            value = prodParameter.split(COLON)[1].trim();
            // 非空校验
            if (StrUtil.isBlank(key) || StrUtil.isBlank(value)) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            if (key.length() > 10) {
                key = key.substring(0, 10);
            }
            if (value.length() > 20) {
                value = value.substring(0, 20);
            }
            ProdParameter dbParameter = new ProdParameter();
            dbParameter.setProdId(product.getProdId());
            dbParameter.setParameterKey(key);
            dbParameter.setParameterValue(value);
//            dbParameter.setParameterKeyEn(key);
//            dbParameter.setParameterValueEn(value);
            prodParameterList.add(dbParameter);
        }
        return prodParameterList;
    }


    public List<ProdParameter> parseParameter(Map<Integer, String> prodParameterLangMap) {
        if (MapUtil.isEmpty(prodParameterLangMap)) {
            return null;
        }
        List<ProdParameter> prodParameterList = new ArrayList<>();
        for (Integer lang : prodParameterLangMap.keySet()) {
            String prodParameterStr = prodParameterLangMap.get(lang);
            if (StrUtil.isBlank(prodParameterStr)) {
                continue;
            }
            if (!hasSemicolon(prodParameterStr)) {
                int length = prodParameterStr.split(COLON).length;
                if (length == 0) {
                    return null;
                }
                if (length != VALID_NUM) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
            }

            // 商品参数名，值都可以相同，不做校验
            String[] prodParameters = prodParameterStr.split(SEMICOLON);
            String key, value;
            for (int i = 0; i < prodParameters.length; i++) {
                String prodParameter = prodParameters[i];
                if (prodParameter.split(COLON).length != VALID_NUM) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
                key = prodParameter.split(COLON)[0].trim();
                value = prodParameter.split(COLON)[1].trim();
                // 非空校验
                if (StrUtil.isBlank(key) || StrUtil.isBlank(value)) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
                if (key.length() > 10) {
                    key = key.substring(0, 10);
                }
                if (value.length() > 20) {
                    value = value.substring(0, 20);
                }
                ProdParameterLang prodParameterLang = new ProdParameterLang();
                prodParameterLang.setLang(lang);
                prodParameterLang.setParameterKey(key);
                prodParameterLang.setParameterValue(value);
                ProdParameter newProdParameter;
                if (prodParameterList.size() > i) {
                    newProdParameter = prodParameterList.get(i);
                } else {
                    newProdParameter = new ProdParameter();
                }
                if (CollUtil.isEmpty(newProdParameter.getProdParameterLangList())) {
                    newProdParameter.setProdParameterLangList(new ArrayList<>());
                }
                newProdParameter.getProdParameterLangList().add(prodParameterLang);
//            dbParameter.setParameterKeyEn(key);
//            dbParameter.setParameterValueEn(value);
//                prodParameterList.add(dbParameter);
            }
        }
        return prodParameterList;
    }

    public boolean hasSemicolon(String str) {
        return Objects.nonNull(str) && !str.isEmpty() && str.contains(SEMICOLON);
    }


    private int writerProdData(ExcelWriter writer, Long shopId, ProductExportParam prod, ProductExportParam esProd, int firstRow, int lastRow, int col, Map<Long, String> categoryMap, Map<Long, String> shopCategoryMap, LangConfig langConfig, Integer lang) {
        // shopId: null-导出商城的商品,跟具体店铺无关
        if (Objects.isNull(shopId)) {
            // 店铺名称
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getShopName());
        }
        Map<Integer, ProdLang> prodLangMap = prod.getProdLangList().stream().collect(Collectors.toMap(ProdLang::getLang, p -> p));
        if (!prodLangMap.keySet().contains(langConfig.getLang())) {
            ProdLang newProdLang = new ProdLang();
            newProdLang.setLang(langConfig.getLang());
            ProdLang prodLang = prodLangMap.get(langManager.getLang(prodLangMap.keySet(), langConfig, lang));
            newProdLang.setProdName(prodLang.getProdName());
            newProdLang.setBrief(prodLang.getBrief());
            prodLangMap.put(langConfig.getLang(), newProdLang);
        }
        // 商品名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            ProdLang prodLang = prodLangMap.get(langItemConfig.getLang());
            if (Objects.nonNull(prodLang)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodLang.getProdName());
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            }
        }
//        // 中文名称
//        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getProdNameZh());
//        // 英文名称
//        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getLang() == 1 ? esProd.getProdNameEn() : null);
        // 类型
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdType.instance(esProd.getProdType()).desc());
        // 类别
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "虚拟商品" : "实物商品");
        // 平台分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, categoryMap.get(prod.getCategoryId()));
        // 店铺分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, shopCategoryMap.get(prod.getShopCategoryId()));
        // 商品图片
        StringBuilder sb = new StringBuilder();
        if(StringUtils.isNotBlank(prod.getImgs())){
            for (String pic : prod.getImgs().split(",")) {
                if(!pic.startsWith("http")&&!pic.startsWith("https")){
                    sb.append(shopConfig.getDomain().getResourcesDomainName()+ "/" + pic + ",");
                }else {
                    sb.append(pic+",");
                }
            }
            sb.deleteCharAt(sb.length()-1);
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, sb.toString());
        // 商品视频
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getVideo());
        // "卖点"
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            ProdLang prodLang = prodLangMap.get(langItemConfig.getLang());
            if (Objects.nonNull(prodLang)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodLang.getBrief());
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            }
        }
//        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getBriefZh());
//        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getLang() == 1 ? esProd.getBriefEn() : null);
        Product.DeliveryModeVO deliveryModeVO = Json.parseObject(prod.getDeliveryMode(), Product.DeliveryModeVO.class);
        StringBuilder deliveryMode = new StringBuilder();
        if (deliveryModeVO != null && !Objects.equals(prod.getMold(), 1)) {
            if (deliveryModeVO.getHasCityDelivery() != null && deliveryModeVO.getHasCityDelivery()) {
                deliveryMode.append("同城配送，");
            }
            if (deliveryModeVO.getHasUserPickUp() != null && deliveryModeVO.getHasUserPickUp()) {
                deliveryMode.append("用户自提，");
            }
            if (deliveryModeVO.getHasShopDelivery() != null && deliveryModeVO.getHasShopDelivery()) {
                deliveryMode.append("快递配送，");
            }
            deliveryMode.deleteCharAt(deliveryMode.length() - 1);
        } else {
            deliveryMode.append("无需快递");
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, deliveryMode);

        // 运费设置
        String transName = prod.getTransName();
        if(Objects.nonNull(prod.getDeliveryTemplateId()) && prod.getDeliveryTemplateId() <= 0) {
            if (Objects.equals(DeliveryTemplateType.FREE_SHIPPING.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREE_SHIPPING.getName();
            } else if (Objects.equals(DeliveryTemplateType.FREIGHT.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREIGHT.getName();
            }
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : transName);
        // 统一运费
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : prod.getDeliveryAmount());
        // 状态
        if (Objects.equals(esProd.getStatus(), ProdStatusEnums.SHOP_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "下架");
        } else if (Objects.equals(esProd.getStatus(), ProdStatusEnums.PLATFORM_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "违规下架");
        } else {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdStatusEnums.instance(esProd.getStatus()).getDesc());
        }
        // 核销次数
        String numStr = "";
        if (Objects.equals(prod.getMold(), 1) && Objects.nonNull(prod.getWriteOffNum())) {
            numStr = prod.getWriteOffNum() == -1 ? "多次核销" : prod.getWriteOffNum() == 0 ? "无需核销" : "单次核销";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, numStr);
        // 多次核销次数
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getWriteOffMultipleCount(), -1) ? "无限次" : prod.getWriteOffMultipleCount());
        // 核销有效期
        String timeStr = "";
        if (Objects.equals(prod.getMold(), 1) && Objects.nonNull(prod.getWriteOffTime())) {
            timeStr = prod.getWriteOffTime() == -1 ? "长期有效" : prod.getWriteOffTime() == 0 ? "自定义" : prod.getWriteOffTime() + "天";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, timeStr);
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffStart());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffEnd());
        String isRefundStr = "";
        //虚拟商品
        if(Objects.equals(prod.getMold(), 1)){
            isRefundStr = Objects.nonNull(prod.getIsRefund()) && prod.getIsRefund() == 0 ? "否" : "是";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, isRefundStr);
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getVirtualRemark());
        //商品参数处理
        Map<Integer, List<ProdParameterLang>> prodParameterLangMap = getProdParameterLangMap(prod, langConfig);
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(langItemConfig.getLang());
            if (CollUtil.isEmpty(prodParameterLangList)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, transParameter(prodParameterLangList));
            }
        }
//        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, transParameter(prod.getProdParameterList(), langConfig, lang));
        return col;
    }

    @NotNull
    private Map<Integer, List<ProdParameterLang>> getProdParameterLangMap(ProductExportParam prod, LangConfig langConfig) {
        Map<Integer, List<ProdParameterLang>> prodParameterLangMap = new HashMap<>();
        for (ProdParameter prodParameter : prod.getProdParameterList()) {
            if (CollUtil.isEmpty(prodParameter.getProdParameterLangList())) {
                continue;
            }
            boolean defaultLang = false;
            for (ProdParameterLang prodParameterLang : prodParameter.getProdParameterLangList()) {
                if (Objects.equals(prodParameterLang.getLang(), langConfig.getLang())) {
                    defaultLang = true;
                }
                List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(prodParameterLang.getLang());
                if (CollUtil.isEmpty(prodParameterLangList)) {
                    prodParameterLangList = new ArrayList<>();
                    prodParameterLangMap.put(langConfig.getLang(), prodParameterLangList);
                }
                prodParameterLangList.add(prodParameterLang);
            }
            if (!defaultLang) {
                List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(langConfig.getLang());
                if (CollUtil.isEmpty(prodParameterLangList)) {
                    prodParameterLangList = new ArrayList<>();
                    prodParameterLangMap.put(langConfig.getLang(), prodParameterLangList);
                }
                prodParameterLangList.add(prodParameter.getProdParameterLangList().get(0));
            }
        }
        return prodParameterLangMap;
    }

    public String transParameter(List<ProdParameterLang> prodParameterList) {
        if (CollUtil.isEmpty(prodParameterList)) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < prodParameterList.size(); i++) {
            ProdParameterLang prodParameter = prodParameterList.get(i);
            builder.append(prodParameter.getParameterKey()).append(":").append(prodParameter.getParameterValue());
            if (i != prodParameterList.size() - 1) {
                builder.append(";");
            }
        }
        return builder.toString();
    }
    private List<String> getHeaderList(Long shopId, LangConfig langConfig, boolean template) {
        List<String> headerList = new ArrayList<>();
        // 店铺名称-平台导入店铺的商品需要用到该字段
        if (Objects.isNull(shopId)) {
            headerList.add(I18nMessage.getMessage("yami.excel.shop.name"));
        }
        // 商品名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            String prodName = I18nMessage.getMessage("yami.excel.prod.name") + "-" + langItemConfig.getName();
            if (Objects.equals(langItemConfig.getLang(), langConfig.getLang())) {
                prodName = prodName  + (template ? I18nMessage.getMessage("yami.excel.required.field") : "");
            }
            headerList.add(prodName);
        }
        // 商品类型
        headerList.add(I18nMessage.getMessage("yami.excel.prod.type") + (template ?  I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 商品类别
        headerList.add(I18nMessage.getMessage("yami.excel.prod.mold") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 平台分类
        headerList.add(I18nMessage.getMessage("yami.excel.platform.category") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 店铺分类
        headerList.add(I18nMessage.getMessage("yami.excel.shop.category") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 商品图片
        headerList.add(I18nMessage.getMessage("yami.excel.prod.pic"));
        // 商品视频
        headerList.add(I18nMessage.getMessage("yami.excel.prod.video"));
        // 商品卖点
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.prod.point") + "-" + langItemConfig.getName());
        }
        // 配送方式
        headerList.add(I18nMessage.getMessage("yami.excel.delivery.method"));
        // 运费选择
        headerList.add(I18nMessage.getMessage("yami.excel.freight.selection") + (template ? I18nMessage.getMessage("yami.excel.freight.selection.required") : ""));
        // 固定运费
        headerList.add(I18nMessage.getMessage("yami.excel.shipping.fee") + (template ? I18nMessage.getMessage("yami.excel.freight.shipping.fee.required") : ""));
        // 商品状态
        headerList.add(I18nMessage.getMessage("yami.excel.prod.status"));
        // 核销次数
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.count") + (template ? I18nMessage.getMessage("yami.excel.virtual.goods.required") : ""));
        // 核销多次
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.many") + (template ? I18nMessage.getMessage("yami.excel.checkout.many.required") : ""));
        // 核销有效期(有效期，x天)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.validity"));
        // 核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.start.time"));
        // 核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.end.time"));
        // 可以退款
        headerList.add(I18nMessage.getMessage("yami.excel.refundable") + (template ? I18nMessage.getMessage("yami.excel.virtual.goods.required") : ""));
        // 虚拟商品留言 格式是[{"name": "xx", "isRequired": true}]
        headerList.add(I18nMessage.getMessage("yami.excel.virtual.goods.message.format"));
        // 商品参数字符串 格式是k1:v1;k2:v2
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.commodity.parameter") + "-" + langItemConfig.getName());
        }
        // 商品sku名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.prod.sku.name") + "-" + langItemConfig.getName());
        }
        // 销售属性组合字符串 格式是p1:v1;p2:v2
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.sales.attribute") + "-" + langItemConfig.getName());
        }
        // 原价
        headerList.add(I18nMessage.getMessage("yami.excel.original.price"));
        // 价格
        headerList.add(I18nMessage.getMessage("yami.excel.price"));
        // 库存
        headerList.add(I18nMessage.getMessage("yami.excel.stock"));
        // 商品编码
        headerList.add(I18nMessage.getMessage("yami.excel.prod.code"));
        // 商品重量
        headerList.add(I18nMessage.getMessage("yami.excel.prod.weight"));
        // 商品体积
        headerList.add(I18nMessage.getMessage("yami.excel.prod.volume"));
        // 库存预警
        headerList.add(I18nMessage.getMessage("yami.excel.sku.stock.warning"));
//        if (Objects.nonNull(shopId)) {
//            String[] headerCn = {"商品中文名称", "商品英文名称", "商品类型", "商品类别", "平台分类", "店铺分类","商品图片","商品视频", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择","固定运费", "商品状态",
//                    "核销次数","多次核销次数", "核销有效期(有效期，x天)", "核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
//                    "可以退款", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
//                    "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积"};
//            String[] headerEn = {"Chinese name", "English name", "Product Type", "Product Category", "platform classification", "store classification","product image","product video", "Chinese selling point",
//                    "English selling point", "delivery method", "Freight selection", "Shipping Fee","product status",
//                    "Checkout Count","Number of multiple write-offs", "Write-off validity(Valid period, x days)", "Checkout Start Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
//                    "Checkout End Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
//                    "Refundable", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
//                    "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
//                    "Inventory", "commodity code", "commodity weight", "commodity volume"};
//            headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
//        }
//        // shopId: null-导出商城的商品,跟具体店铺无关
//        else {
//            String[] headerCn = {"店铺名称", "商品中文名称", "商品英文名称", "商品类型", "商品类别", "平台分类", "店铺分类","商品图片","商品视频", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择","固定运费", "商品状态",
//                    "核销次数","多次核销次数", "核销有效期(有效期，x天)", "核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
//                    "可以退款", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
//                    "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积"};
//            String[] headerEn = {"Store name", "Chinese name", "English name", "Product Type", "Product Category", "platform classification", "store classification","product image","product video", "Chinese selling point",
//                    "English selling point", "delivery method", "Freight selection", "Shipping Fee","product status",
//                    "Checkout Count","Number of multiple write-offs ", "Write-off validity(Valid period, x days)", "Checkout Start Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
//                    "Checkout End Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
//                    "Refundable", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
//                    "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
//                    "Inventory", "commodity code", "commodity weight", "commodity volume"};
//            headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
//        }
        return headerList;
    }

    private Sheet getSheet(ExcelWriter writer, Long shopId, Integer langSize) {
        Sheet sheet = writer.getSheet();
        int index = 0;
        if (Objects.isNull(shopId)) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 商品名称
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 60 * 256);
        }
        // 商品类型
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品类别
        sheet.setColumnWidth(index++, 20 * 256);
        // 平台分类
        sheet.setColumnWidth(index++, 40 * 256);
//        for (int i = 0; i < langSize; i++) {
//            sheet.setColumnWidth(index++, 40 * 256);
//        }
//        sheet.setColumnWidth(index++, 40 * 256);
        // 店铺分类
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品图片
        sheet.setColumnWidth(index++, 20 * 256);
//        setComment(sheet.getRow(1).getCell(index++),"1、选填，请填写图片在图片服务器的URL地址;\n" +
//                "2、最多可上传9张，不同图片请以“,”隔开\n" +
//                "3、归属于同一SPU的SKU， 只需填写第一条SKU的商品图片;",sheet);
        // 商品视频
        sheet.setColumnWidth(index++, 20 * 256);
//        setComment(sheet.getRow(1).getCell(index++),"1、选填，请填写视频在视频服务器的URL地址;\n" +
//                "2、只能添加一个视频链接;\n" +
//                "3、 归属同一SPU的SKU,只需填写第一条SKU的商品视频;\n" +
//                "4、 视频仅支持mp4格式,最大限制50M;",sheet);
        // 商品卖点
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 配送方式
        sheet.setColumnWidth(index++, 20 * 256);
        // 运费选择
        sheet.setColumnWidth(index++, 40 * 256);
        // 固定运费
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品状态
        sheet.setColumnWidth(index++, 20 * 256);
        // 核销次数
        sheet.setColumnWidth(index++, 20 * 256);
        // 核销多次
        sheet.setColumnWidth(index++, 20 * 256);
        // 核销有效期(有效期，x天)
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)
        sheet.setColumnWidth(index++, 40 * 256);
        // 可以退款
        sheet.setColumnWidth(index++, 20 * 256);
        // 虚拟商品留言 格式是[{"name": "xx", "isRequired": true}]
        sheet.setColumnWidth(index++, 40 * 256);
        // 商品参数字符串 格式是k1:v1;k2:v2
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 商品sku名称
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 销售属性组合字符串 格式是p1:v1;p2:v2
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 原价
        sheet.setColumnWidth(index++, 30 * 256);
        // 价格
        sheet.setColumnWidth(index++, 30 * 256);
        // 库存
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品编码
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品重量
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品体积
        sheet.setColumnWidth(index++, 30 * 256);
        return sheet;
    }


    public static void setComment(Cell cell, String text, Sheet sheet){
        ClientAnchor anchor = new XSSFClientAnchor();
        anchor.setDx1(0);
        anchor.setDx2(0);
        anchor.setDy1(0);
        anchor.setDy2(0);
        anchor.setCol1(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setCol2(cell.getColumnIndex() + 5);
        anchor.setRow2(cell.getRowIndex() + 6);
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        comment.setString(new XSSFRichTextString(text));
        cell.setCellComment(comment);
    }
}
